package org.example.methods;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class Methods {
    private static final String URL = "jdbc:mysql://192.168.8.115:3306/attendance";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static ArrayList<Integer> table1() throws ClassNotFoundException, SQLException {
        //返回ArrayList<Integer>类型的列表，从数据库table1表中检索数据

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM table1";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<Integer> list = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            int A = resultSet.getInt("A");  //A出勤
            int L = resultSet.getInt("L");  //L缺勤
            //使用getInt方法获取名为 "A" 和 "L" 的两列的值。
            // 这两个值被分别存储在int类型的变量A和L中。
            list.add(A);
            list.add(L);
            //将数据添加到列表中
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
        return list;
    }


    //这段代码定义了一个静态方法 table2，该方法的主要目的是从MySQL数据库中查询table2表的前5条记录，
    // 并将查询结果中的name字段和A字段分别存储到两个ArrayList中，然后将这两个ArrayList存储到一个HashMap中返回。
    public static HashMap<String, ArrayList<?>> table2() throws ClassNotFoundException, SQLException {
       //throws ClassNotFoundException, SQLException:
        // 表示该方法可能会抛出ClassNotFoundException（当找不到数据库驱动类时）和SQLException（当数据库操作出错时）异常。

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM table2 limit 5";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name = new ArrayList<>();
        ArrayList<Integer> list_A = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name.add(resultSet.getString("name"));
            list_A.add(resultSet.getInt("A"));
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

        //创建一个新的HashMapmap。
        // 使用map.put("name", list_name)和map.put("A", list_A)将list_name和list_A作为值添加到HashMap中，
        // 键分别为"name"和"A"。
        //返回这个HashMap。
        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name", list_name);
        map.put("A", list_A);

        return map;
    }

    public static HashMap<String, ArrayList<?>> table3() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM table3 limit 3";//定义了一个 SQL 查询语句，用于从 table3 表中获取最多 3 条记录。
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name = new ArrayList<>();
        ArrayList<Integer> list_L = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name.add(resultSet.getString("name"));
            list_L.add(resultSet.getInt("L"));
        }
        //在循环中，遍历结果集的每一行，
        // 并使用 getString 和 getInt 方法从结果集中提取特定列的值，然后将其添加到相应的列表中
        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name", list_name);
        map.put("L", list_L);
         //最后，创建一个新的 HashMap 对象，并将之前创建的两个列表作为值添加到该映射中，
        // 键分别为 "name" 和 "L"。然后返回这个 HashMap 对象。
        return map;
    }

    public static HashMap<String, ArrayList<?>> CF1() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT name1, SUM(CONVERT(related, DECIMAL(10, 6))) AS related_sum\n" +
                "FROM CF\n" +
                "GROUP BY name1\n" +
                "ORDER BY related_sum DESC\n" +
                "LIMIT 6;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name1 = new ArrayList<>();
        ArrayList<Double> list_related = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name1.add(resultSet.getString("name1"));
            list_related.add(resultSet.getDouble("related_sum"));

        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name1", list_name1);
        map.put("related", list_related);
        System.out.println(map);

        return map;
    }
    public static HashMap<String, ArrayList<?>> CF2(String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = String.format("SELECT * FROM CF WHERE name1='%s' ORDER BY related DESC LIMIT 3;", name);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name1 = new ArrayList<>();
        ArrayList<String> list_name2 = new ArrayList<>();
        ArrayList<Double> list_related = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name1.add(resultSet.getString("name1"));
            list_name2.add(resultSet.getString("name2"));
            double related = Double.parseDouble(resultSet.getString("related"));
            list_related.add(related);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name1", list_name1);
        map.put("name2", list_name2);
        map.put("related", list_related);
        return map;
    }
}








